package cn.trkj.server.dao;

import cn.trkj.server.dto.FileDTO;
import cn.trkj.server.util.DBUtil;
import org.eclipse.jetty.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author admin
 * @date 2021/2/20 3:30 下午
 */
public class FileDao {

    /**
     * 创建表
     *
     * @throws SQLException
     */
    public void createTable() throws SQLException {
//        Connection conn = DBUtil.getConnection();
//        String sql = "CREATE TABLE xn_file ( " +
//                "id VARCHAR(100), " +
//                "name VARCHAR(100), " +
//                "type VARCHAR(100), " +
//                "size VARCHAR(100), " +
//                "location VARCHAR(200), " +
//                "envelope VARCHAR(200), " +
//                "time TIMESTAMP )";
//        PreparedStatement ptmt = conn.prepareStatement(sql);
//        ptmt.executeUpdate();
    }

    /**
     * 添加
     *
     * @param fileDTO
     * @throws SQLException
     * @throws ParseException
     */
    public void insert(FileDTO fileDTO) throws SQLException {
        Connection conn = DBUtil.getConnection();
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("insert into air_file(");
        stringBuffer.append(" id,name,type,location,size,envelope,time,status,jqName ");
        if (StringUtil.isNotBlank(fileDTO.getCarcolor())) {
            stringBuffer.append(",carcolor");
        }
        if (StringUtil.isNotBlank(fileDTO.getCartype())) {
            stringBuffer.append(",cartype");
        }
        if (StringUtil.isNotBlank(fileDTO.getColor())) {
            stringBuffer.append(",color");
        }
        if (StringUtil.isNotBlank(fileDTO.getConfidencelevel())) {
            stringBuffer.append(",confidencelevel");
        }
        if (StringUtil.isNotBlank(fileDTO.getFogpos())) {
            stringBuffer.append(",fogpos");
        }
        if (StringUtil.isNotBlank(fileDTO.getIdroad())) {
            stringBuffer.append(",idroad");
        }
        if (StringUtil.isNotBlank(fileDTO.getLicense())) {
            stringBuffer.append(",license");
        }
        if (StringUtil.isNotBlank(fileDTO.getRingelmann())) {
            stringBuffer.append(",ringelmann");
        }
        stringBuffer.append(") values (");
        stringBuffer.append(" ?,?,?,?,?,?,?,?,? ");
        if (StringUtil.isNotBlank(fileDTO.getCarcolor())) {
            stringBuffer.append(",?");
        }
        if (StringUtil.isNotBlank(fileDTO.getCartype())) {
            stringBuffer.append(",?");
        }
        if (StringUtil.isNotBlank(fileDTO.getColor())) {
            stringBuffer.append(",?");
        }
        if (StringUtil.isNotBlank(fileDTO.getConfidencelevel())) {
            stringBuffer.append(",?");
        }
        if (StringUtil.isNotBlank(fileDTO.getFogpos())) {
            stringBuffer.append(",?");
        }
        if (StringUtil.isNotBlank(fileDTO.getIdroad())) {
            stringBuffer.append(",?");
        }
        if (StringUtil.isNotBlank(fileDTO.getLicense())) {
            stringBuffer.append(",?");
        }
        if (StringUtil.isNotBlank(fileDTO.getRingelmann())) {
            stringBuffer.append(",?");
        }
        stringBuffer.append(" )");
        PreparedStatement ptmt = conn.prepareStatement(stringBuffer.toString());
        ptmt.setString(1, fileDTO.getId());
        ptmt.setString(2, fileDTO.getName());
        ptmt.setString(3, fileDTO.getType());
        ptmt.setString(4, fileDTO.getLocation());
        ptmt.setString(5, fileDTO.getSize());
        ptmt.setString(6, fileDTO.getEnvelope());
        ptmt.setString(7, fileDTO.getTime());
        ptmt.setString(8, fileDTO.getStatus());
        ptmt.setString(9, fileDTO.getJqName());
        if (StringUtil.isNotBlank(fileDTO.getCarcolor())) {
            ptmt.setString(10, fileDTO.getCarcolor());
        }
        if (StringUtil.isNotBlank(fileDTO.getCartype())) {
            ptmt.setString(11, fileDTO.getCartype());
        }
        if (StringUtil.isNotBlank(fileDTO.getColor())) {
            ptmt.setString(12, fileDTO.getColor());
        }
        if (StringUtil.isNotBlank(fileDTO.getConfidencelevel())) {
            ptmt.setString(13, fileDTO.getConfidencelevel());
        }
        if (StringUtil.isNotBlank(fileDTO.getFogpos())) {
            ptmt.setString(14, fileDTO.getFogpos());
        }
        if (StringUtil.isNotBlank(fileDTO.getIdroad())) {
            ptmt.setString(15, fileDTO.getIdroad());
        }
        if (StringUtil.isNotBlank(fileDTO.getLicense())) {
            ptmt.setString(16, fileDTO.getLicense());
        }
        if (StringUtil.isNotBlank(fileDTO.getRingelmann())) {
            ptmt.setString(17, fileDTO.getRingelmann());
        }
        ptmt.executeUpdate();
    }

    /**
     * 根据ID查询
     *
     * @return
     * @throws SQLException
     */
    public FileDTO findById(String id) throws SQLException {
        Connection conn = DBUtil.getConnection();
        String sql = "select id,name, type, location,size,envelope,time from file where id = ?";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setString(1, id);
        ResultSet rs = ptmt.executeQuery();
        FileDTO fileDTO = null;
        while (rs.next()) {
            fileDTO = new FileDTO();
            fileDTO.setId(rs.getString("id"));
            fileDTO.setName(rs.getString("name"));
            fileDTO.setType(rs.getString("type"));
            fileDTO.setLocation(rs.getString("location"));
            fileDTO.setSize(rs.getString("size"));
            fileDTO.setEnvelope(rs.getString("envelope"));
            fileDTO.setTime(rs.getString("time"));
        }
        return fileDTO;
    }
}